package com.ruoyi.websql.service.impl;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.date.TimeInterval;
import cn.hutool.core.thread.ThreadUtil;
import cn.hutool.core.util.ObjectUtil;
import com.alibaba.druid.DbType;
import com.alibaba.fastjson2.JSON;
import com.ruoyi.common.core.domain.AjaxResult;
import com.ruoyi.websql.config.DataSourceFactory;
import com.ruoyi.websql.config.JdbcUtils;
import com.ruoyi.websql.config.SqlParserHandler;
import com.ruoyi.websql.domain.SqlDatasource;
import com.ruoyi.websql.domain.bean.MetaTreeTable;
import com.ruoyi.websql.domain.bean.SqlDataSourceIndexMeta;
import com.ruoyi.websql.domain.bean.SqlDataSourceMeta;
import com.ruoyi.websql.domain.bean.SqlDataSourceTableMeta;
import com.ruoyi.websql.domain.vo.SqlPageDataSourceVO;
import com.ruoyi.websql.mapper.SqlDatasourceMapper;
import com.ruoyi.websql.model.ExecuteSql;
import com.ruoyi.websql.model.SqlExecuteResultVo;
import com.ruoyi.websql.model.SqlParserVo;
import com.ruoyi.websql.service.ISqlDatasourceService;
import com.ruoyi.websql.util.TableFieldSqlUtils;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.sql.*;
import java.util.*;
import java.util.concurrent.Future;
import java.util.stream.Collectors;

import static com.ruoyi.common.core.domain.AjaxResult.DATA_TAG;

/**
 * ***Service业务层处理
 *
 * @author xudengyu
 * @date 2025-03-18
 */
@Service
@Slf4j
public class SqlDatasourceServiceImpl implements ISqlDatasourceService {
    @Resource
    private SqlDatasourceMapper sqlDatasourceMapper;

    @Override
    public int deleteSqlDatasourceById(Long id) {
        return sqlDatasourceMapper.deleteSqlDatasourceById(id);
    }

    @Override
    public boolean checkConnectName(String dbName) {
        List<String> list = selectSqlDatasourceList(new SqlDatasource()).stream().map(SqlDatasource::getDbName).collect(Collectors.toList());
        return !list.contains(dbName);

    }

    @Override
    public AjaxResult executeSqlNew(ExecuteSql sql) {
        try {
            List<SqlParserVo> parserVoList = SqlParserHandler.getParserVoNew(sql.getDataBaseName(), sql.getSqlText());
            if (parserVoList.isEmpty()) {
                return AjaxResult.error("解析SQL失败,返回为空请重试!");
            }
            if (sql.isExport()) {
                long count = parserVoList.stream().map(SqlParserVo::getMethodType).filter(s -> !s.equals("SELECT")).count();
                if (count > 0) {
                    return AjaxResult.error("导出数据时,不支持非查询语句执行!");
                }
            }
            List<SqlExecuteResultVo> resultVos = new ArrayList<>(parserVoList.size());
            for (SqlParserVo sqlParserVo : parserVoList) {
//                SqlParserVo sqlParserVo = parserVoList.get(0);
                TimeInterval timer = DateUtil.timer();
                SqlExecuteResultVo vo = new SqlExecuteResultVo();
                vo.setTableNameList(JSON.toJSONString(sqlParserVo.getTableNameList()));
                vo.setExecuteType(sqlParserVo.getMethodType());
                vo.setParserSql(sqlParserVo.getSqlContent());
                vo.setDataBaseKey(sql.getDataBaseName());
//                System.err.println(vo);
                if (SqlParserHandler.SELECT.equals(sqlParserVo.getMethodType())) {
                    Map<String, Object> moreResult = JdbcUtils.findMoreResultNew(sql.getDataBaseName(), sqlParserVo.getSqlContent(), new ArrayList<>());
                    vo.setData(moreResult.get("data"));
                    vo.setStatus(Integer.parseInt(moreResult.get("code").toString()));
                    vo.setType(0);
                    vo.setErrorMessage(moreResult.get("msg").toString());
                } else {
                    Map<String, Object> moreResult = JdbcUtils.updateByPreparedStatement(sql.getDataBaseName(), sqlParserVo.getSqlContent(), new ArrayList<>());
                    vo.setType(1);
                    vo.setData(moreResult.get("data"));
                    vo.setStatus(Integer.parseInt(moreResult.get("code").toString()));
                    vo.setErrorMessage(moreResult.get("msg").toString());
                }
                vo.setTime(timer.intervalRestart());
                resultVos.add(vo);
            }
            return AjaxResult.success(resultVos);
        } catch (Exception e) {
            e.printStackTrace();
            return AjaxResult.error(e.getMessage());
        } finally {

        }
    }

    @Override
    public List<SqlPageDataSourceVO> findSqlPageDataSourceList() {
        List<SqlDatasource> sqlDatasourceAll = sqlDatasourceMapper.selectSqlDatasourceList(new SqlDatasource());
        ArrayList<SqlPageDataSourceVO> res = new ArrayList<>();
        sqlDatasourceAll.forEach(i -> {
            SqlPageDataSourceVO sqlPageDataSourceVO = new SqlPageDataSourceVO()
                    .setId(i.getID())
                    .setValue(i.getDbName());
            res.add(sqlPageDataSourceVO);
        });
        return res;
    }

    private MetaTreeTable createFieldNode(SqlDataSourceTableMeta fieldMeta) {
        MetaTreeTable field = new MetaTreeTable();
        field.setField("field");
        String title = Objects.equals(fieldMeta.getComment(), "") || fieldMeta.getComment() == null ? fieldMeta.getColumnName() : String.join(":", fieldMeta.getColumnName(), fieldMeta.getComment());
        field.setTitle(title);
        field.setId(fieldMeta.getColumnName());
        field.setDisabled(true);
        return field;
    }

    private MetaTreeTable createTableNode(SqlDataSourceMeta meta, int tableCount) {
        List<MetaTreeTable> fieldList = new ArrayList<>();
        for (SqlDataSourceTableMeta dataSourceTableMeta : meta.getTablesMetaList()) {
            MetaTreeTable item = createFieldNode(dataSourceTableMeta);
            fieldList.add(item);
        }
        MetaTreeTable table = new MetaTreeTable();
        table.setField("table");
        table.setTableCount(tableCount);
        table.setFieldCount(fieldList.size());
        table.setId(meta.getTableName());
        String title = Objects.equals(meta.getTableComment(), "") || meta.getTableComment() == null ? meta.getTableName() : String.join(":", meta.getTableName(), meta.getTableComment());
        table.setTitle(title);
        table.setTableMeta(meta);
        table.setChildren(fieldList);
        return table;
    }

    public AjaxResult findMetaTable(String database, String table) {
        SqlDataSourceMeta dataSourceMeta = new SqlDataSourceMeta();
        try {
            Future<SqlDataSourceMeta> tableMetaFuture = ThreadUtil.execAsync(() -> JdbcUtils.getTableMetaNew(database, table));
            Future<List<SqlDataSourceTableMeta>> columnsMetaFuture = ThreadUtil.execAsync(() -> JdbcUtils.getColumnsMetaNew(database, table));
            Future<List<SqlDataSourceIndexMeta>> indexInfoMetaFuture = ThreadUtil.execAsync(() -> JdbcUtils.getIndexInfoMetaNew(database, table));
            Future<List<SqlDataSourceTableMeta>> keyMetaFuture = ThreadUtil.execAsync(() -> JdbcUtils.getKeyMetaNew(database, table));
            SqlDataSourceMeta tableMeta = tableMetaFuture.get();
            if (ObjectUtil.isNull(tableMeta)) {
                columnsMetaFuture.cancel(true);
                indexInfoMetaFuture.cancel(true);
                keyMetaFuture.cancel(true);
                return AjaxResult.error("数据库中没有[" + table + "]信息!");
            }
            dataSourceMeta = JdbcUtils.getDataSourceMetaNew(database, table);
            dataSourceMeta.setDatabaseName(tableMeta.getDatabaseName());
            dataSourceMeta.setTableType(tableMeta.getTableType());
            dataSourceMeta.setTableComment(tableMeta.getTableComment());
            dataSourceMeta.setTableSchema(tableMeta.getTableSchema());
            List<SqlDataSourceTableMeta> tableMetas = columnsMetaFuture.get();
            dataSourceMeta.setTablesMetaList(tableMetas);
            List<SqlDataSourceTableMeta> columnsMeta = keyMetaFuture.get();
            dataSourceMeta.setTablesKeysMetaList(columnsMeta);
            List<SqlDataSourceIndexMeta> dataSourceIndexMetas = indexInfoMetaFuture.get();
            dataSourceMeta.setTablesIndexMetaList(dataSourceIndexMetas);
            // [data[0].tableMeta]
//            System.err.println("[findMetaTable] dataSourceMeta>> "+dataSourceMeta);
            return AjaxResult.success(dataSourceMeta);
        } catch (Exception e) {
            e.printStackTrace();
            return AjaxResult.success(dataSourceMeta);
        }
    }

    // 查询数据库所有表及字段
    @Override
    public AjaxResult findTableFieldByDatabase(String database) {
        Map<String, List<String>> resultMap = new HashMap<>();
        String viewSql = TableFieldSqlUtils.getViewSqlNew(database);
//        System.err.println("viewSql>> "+viewSql);
        Map<String, Object> map = JdbcUtils.findMoreResultNew(database, viewSql, new ArrayList<>());
        List<Map> list = (List<Map>) map.get("data");
        resultMap = list.stream().collect(Collectors.groupingBy(s -> s.get("TABLE_NAME").toString(), Collectors.mapping(s -> s.get("TABLE_FIELD").toString(), Collectors.toList())));
//        System.err.println("resultMap>> "+resultMap);
        return AjaxResult.success(resultMap);
    }

    private MetaTreeTable createDataSourceNode(SqlDatasource item) {
        MetaTreeTable metaTreeTable = new MetaTreeTable()
                .setId(item.getID().toString())
                .setTitle(item.getDbName())
                .setField("database");
        return metaTreeTable;
    }

    // 获取树形元数据信息
    @Override
    public List<MetaTreeTable> metaTreeTableList() {
        List<MetaTreeTable> resultList = new ArrayList<>();
        List<SqlDatasource> databaseList = selectSqlDatasourceList(new SqlDatasource());
        if (databaseList == null || databaseList.isEmpty()) {
            return resultList;
        }
        for (SqlDatasource item : databaseList) {
            MetaTreeTable metaTreeTable = createDataSourceNode(item);
            resultList.add(metaTreeTable);
        }
        for (MetaTreeTable database : resultList) {
            AjaxResult table = findTableFieldByDatabase(database.getTitle());
            if (!table.isSuccess()) continue;
            // 数据源表结构
            Map<String, List<String>> tableMap = (Map<String, List<String>>) table.get(DATA_TAG);
            List<MetaTreeTable> tableList = new ArrayList<>();
            int tableCount = tableMap.size();
            tableMap.forEach((k, v) -> {
                AjaxResult tableField = this.findMetaTable(database.getTitle(), k);
                SqlDataSourceMeta tableFieldData = (SqlDataSourceMeta) tableField.get(DATA_TAG);
                if (ObjectUtil.isNotNull(tableFieldData)) {
                    SqlDataSourceMeta item = new SqlDataSourceMeta();
                    BeanUtil.copyProperties(tableFieldData, item);
                    tableList.add(createTableNode(item, tableCount));
                }
            });
            if (ObjectUtil.isNotNull(tableList) && !tableList.isEmpty()) {
                MetaTreeTable metaTreeTable = tableList.get(0);
                SqlDataSourceMeta tableMeta = metaTreeTable.getTableMeta();
                SqlDataSourceMeta item = new SqlDataSourceMeta();
                BeanUtil.copyProperties(tableMeta, item);
                item.setTablesKeysMetaList(null);
                item.setTablesIndexMetaList(null);
                item.setTablesMetaList(null);
                item.setTableComment(null);
                item.setTableName(null);
                database.setTableCount(metaTreeTable.getTableCount());
                database.setTableMeta(item);
            }
            database.setChildren(tableList);
        }
        return resultList;
    }

    // 数据源连接测试
    @Override
    public String testDatasource(SqlDatasource sqlDatasource) {
        Connection conn = null;
        PreparedStatement pre = null;
        ResultSet rs = null;
        try {
            conn = DriverManager.getConnection(sqlDatasource.getDbUrl().trim(),
                    sqlDatasource.getDbAccount().trim(), sqlDatasource.getDbPassword().trim());
            DbType jdbcType = DataSourceFactory.getDbTypeByJdbcUrl(sqlDatasource.getDbUrl().trim(), sqlDatasource.getDriverClass());
            //odps特殊，提交实例运行
            if (jdbcType.equals(DbType.odps)) {
                if (ObjectUtil.isEmpty(conn.getMetaData().getDatabaseProductName())) {
                    return "连接失败,获取odps描述为空!";
                }
                return "连接成功!";
            }
            pre = conn.prepareStatement(sqlDatasource.getDbCheckUrl());
            rs = pre.executeQuery();
            if (rs.next()) {
                return "连接成功!";
            } else {
                return "连接失败,返回结果集为空!";
            }
        } catch (Exception e) {
            e.printStackTrace();
            log.error(e.getMessage());
            return "连接失败,error:" + e.getMessage();
        } finally {
            JdbcUtils.releaseConn(rs, pre, conn);
        }
    }

    /**
     * 查询***
     *
     * @param dbAccount ***主键
     * @return ***
     */
    @Override
    public SqlDatasource selectSqlDatasourceByDbAccount(String dbAccount) {
        return sqlDatasourceMapper.selectSqlDatasourceByDbAccount(dbAccount);
    }

    /**
     * 查询***列表
     *
     * @param sqlDatasource ***
     * @return ***
     */
    @Override
    public List<SqlDatasource> selectSqlDatasourceList(SqlDatasource sqlDatasource) {
        return sqlDatasourceMapper.selectSqlDatasourceList(sqlDatasource);
    }

    /**
     * 新增***
     *
     * @param sqlDatasource ***
     * @return 结果
     */
    @Override
    public int insertSqlDatasource(SqlDatasource sqlDatasource) {
        try {
            com.ruoyi.websql.factory.DataSourceFactory.saveDataSource(sqlDatasource);
            return sqlDatasourceMapper.insertSqlDatasource(sqlDatasource);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 修改***
     *
     * @param sqlDatasource ***
     * @return 结果
     */
    @Override
    public int updateSqlDatasource(SqlDatasource sqlDatasource) {
        return sqlDatasourceMapper.updateSqlDatasource(sqlDatasource);
    }

    /**
     * 批量删除***
     *
     * @param dbAccounts 需要删除的***主键
     * @return 结果
     */
    @Override
    public int deleteSqlDatasourceByDbAccounts(String[] dbAccounts) {
        for (String i : dbAccounts) {
            com.ruoyi.websql.factory.DataSourceFactory.removeDataSource(selectSqlDatasourceByDbAccount(i).getDbName().trim());
        }
        return sqlDatasourceMapper.deleteSqlDatasourceByDbAccounts(dbAccounts);
    }

    /**
     * 删除***信息
     *
     * @param dbAccount ***主键
     * @return 结果
     */
    @Override
    public int deleteSqlDatasourceByDbAccount(String dbAccount) {
        return sqlDatasourceMapper.deleteSqlDatasourceByDbAccount(dbAccount);
    }
}
